from django.db import models

# Create your models here.
import sqlite3
from gs_scheduler.management.commands.config import MYSQL_HOST,MYSQL_NAME,MYSQL_PORT,MYSQL_PASSWORD,MYSQL_USER,MYSQL_CHARSET
from datetime import datetime,timedelta
from django.conf import settings

import pymysql
from concurrent.futures import ThreadPoolExecutor

#时间戳转时间字符串
def timestamp_to_time_str(timestamp):
    # 使用 datetime 模块将时间戳转换为 datetime 对象
    dt = datetime.fromtimestamp(timestamp)
    # 将 datetime 对象格式化为时间字符串
    time_str = dt.strftime('%Y-%m-%d %H:%M:%S')
    return time_str

class MysqlDB:
    DATETIME_FORMAT = '%Y-%m-%d %H:%M:%S'
    # 创建数据库连接池
    def __init__(self):
        self.conn = pymysql.connect(
            host=MYSQL_HOST,
            port=MYSQL_PORT,
            user=MYSQL_USER,
            password=MYSQL_PASSWORD,
            db=MYSQL_NAME,
            charset=MYSQL_CHARSET,
            cursorclass=pymysql.cursors.DictCursor
        )

    # 执行数据库增删改查
    def _execute_sql(self,query):
        sql,args = query
        try:
            with self.conn.cursor() as cursor:
                # 执行sql语句
                if args:
                    if isinstance(args,(tuple,list)):
                        # args = (value1,value2)
                        cursor.execute(sql,args)
                    else:
                        # args = value1
                        cursor.execute(sql,(args,))
                else:
                    cursor.execute(sql)
                # 不同类型sql，设置不同返回值
                if sql.strip().lower()[:6] == 'select':
                    #查询语句
                    rows = cursor.fetchall() or []
                    return rows
                elif sql.strip().lower()[:4] == 'show':
                    #查看表是否存在
                    result = cursor.fetchall()
                    return result
                else:
                    #增删改语句
                    self.conn.commit()
                    return True
        except Exception as e:
            print('sql执行失败',e)
            pass

    # 线程池执行sql语句
    def start_sql_with_pool(self, sql:str,args=None):
        with ThreadPoolExecutor() as executor:
            result = executor.submit(self._execute_sql, (sql,args)).result()
            return result

    #创建记录定时任务历史表(调度器使用)
    def create_apscheduler_history(self):
        #创建表语句：表名不能是占位符
        sql = """CREATE TABLE apscheduler_history (
                id INTEGER AUTO_INCREMENT PRIMARY KEY,
                job_id VARCHAR(128),
                run_time DATETIME,
                is_error TINYINT,
                error_msg VARCHAR(256) NULL)"""
        #判断表存在不，不存在再创建
        results = self.start_sql_with_pool('SHOW TABLES')
        is_exist = False
        for dic in results:
            if dic['Tables_in_{}'.format(MYSQL_NAME,)] == 'apscheduler_history':
                is_exist = True
        #表不存在才创建
        if not is_exist:
            # print('表不存在，执行创建表')
            create = self.start_sql_with_pool(sql)
            # 创建索引
            self.start_sql_with_pool(db.conn.cursor().execute("CREATE INDEX run_time_index ON apscheduler_history (run_time)"))
        return True

    # 将任务运行的结果记录到数据库中（调度器使用）
    def insert_into_apscheduler_history(self, data_list: list):
        if len(data_list) == 4:  # 异常执行的任务
            sql = """
            INSERT INTO apscheduler_history (job_id,run_time,is_error,error_msg) VALUES (%s,%s,%s,%s)
            """
        else:  # 正常执行的任务
            sql = """
            INSERT INTO apscheduler_history (job_id,run_time,is_error) VALUES (%s,%s,%s)
            """
        #使用线程池执行插入语句
        self.start_sql_with_pool(sql,data_list)

    # 删除8小时前的历史记录（调度器使用）
    def delete_8hour_before_history(self):
        before_8hour = (datetime.now() - timedelta(hours=8)).strftime('%Y-%m-%d %H:%M:%S')
        sql = '''
        DELETE FROM apscheduler_history WHERE run_time < %s'''
        self.start_sql_with_pool(sql,(before_8hour,))

    # 查询每个任务的下次运行时间（api展示）
    def fetch_all_next_run_time(self):
        sql = 'SELECT id,next_run_time FROM apscheduler_jobs'
        # 获取查询结果
        rows = self.start_sql_with_pool(sql)
        for row in rows:
            row['next_run_time'] = timestamp_to_time_str(row['next_run_time'])
        return rows

    # 查询所有任务最近10次运行记录（api展示）
    def fetch_all_run_history(self):
        '''
        :param query:'SELECT id,next_run_time,job_state FROM apscheduler_jobs'
        :return:
        '''
        #获取所有定时任务id
        data_list = self.fetch_all_next_run_time()
        id_list = (dic.get('id') for dic in data_list)
        # 创建一个游标对象
        sql = """
           SELECT id,job_id,run_time FROM apscheduler_history WHERE is_error=0 AND job_id = %s ORDER BY run_time DESC LIMIT 10
           """
        ret_list = []
        for id in id_list:
            # 执行查询
            rows = self.start_sql_with_pool(sql,(id,)) or []
            dic = {'id': id, 'last_run_time': [], 'msg': '最近10次运行时间'}
            for row in rows:
                run_time = row.get('run_time')
                run_time = run_time.strftime(self.DATETIME_FORMAT) if isinstance(run_time,datetime) else run_time
                dic['last_run_time'].append(run_time)
            ret_list.append(dic)
        return ret_list

    # 获取任务最近运行失败情况(api展示)
    def fetch_all_error_history(self):
        data_list = self.fetch_all_next_run_time()
        id_list = (dic.get('id') for dic in data_list)
        sql = """
        SELECT run_time,error_msg FROM apscheduler_history WHERE is_error=1 AND job_id = %s ORDER BY id DESC LIMIT 5
        """
        ret_list = []
        for id in id_list:
            # 获取查询结果
            rows = self.start_sql_with_pool(sql, (id,)) or []
            dic = {'id': id, 'last_run_time': [], 'msg': '最近5次执行失败'}
            for row in rows:
                run_time = row.get('run_time')
                run_time = run_time.strftime(self.DATETIME_FORMAT) if isinstance(run_time,datetime) else run_time
                error = row.get('error_msg')
                dic['last_run_time'].append({'run_time': run_time, 'error': error})
            ret_list.append(dic)
        return ret_list

    # 关闭连接
    def close(self):
        self.conn.close()



if __name__ == '__main__':
    db = MysqlDB()
    db.create_apscheduler_history()
    # for i in range(1,10):
    #     db.insert_into_apscheduler_history(['send_to_big_data','2024-05-01 13:{}:12'.format(str(i).zfill(2)),1,'执行失败了'])
    # db.delete_8hour_before_history()
    # ret = db.fetch_all_run_history()
    # print(ret,'history')
    # ret = db.fetch_all_next_run_time()
    # print(ret,'next')
    # ret = db.fetch_all_error_history()
    # print(ret,'error')
    db.close()



